package com.wireless.service;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import javax.annotation.Resource;

import org.hibernate.Query;
import org.hibernate.SessionFactory;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.wireless.base.BaseServiceImpl;
import com.wireless.entity.Ordertbl;
import com.wireless.entity.Purchase;
import com.wireless.entity.Tabletbl;

@Transactional
@Service
public class OrderServiceImpl extends BaseServiceImpl implements OrderService {
	SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd"); 
	Date nowDate=new Date(); 
	String dateTime=format.format(nowDate);
	@Resource
	private SessionFactory sessionFactory;

	// ���pageSize��pageNow��ʾ��Ϣ
	public List getOrderList() {
		String hql = "from Ordertbl order by orderTime desc";
		return getResult(hql, null);
	}

	// select * from ordertbl where orderTime between '2018-07-04 21:51:38' and
	// '2018-07-06 00:00:00';
	// select count(*),substr(t.orderTime,1,10) from ordertbl t where
	// t.orderTime like '2018-07%' group by substr(t.orderTime,1,10)
	// select sum(ord.num*m.price),substr(t.orderTime,1,10) from ordertbl
	// t,orderdetailtbl ord,menutbl m where t.id=ord.orderID and ord.menuID=m.id
	// and t.orderTime like '2018-07%' group by substr(t.orderTime,1,10)
	public int getPageCount(int pageSize) {
		String hql = "select count(*) from Ordertbl";
		return this.queryPageCount(hql, null, pageSize);
	}

	public boolean changeOrderPayFlag(Integer id) {
		Ordertbl order = (Ordertbl) findById(Ordertbl.class, id);
		if (order.getIdPay().equals(1)) {
			order.setIdPay(0);
		} else {
			order.setIdPay(1);
		}
		try {
			update(order);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}

	public Ordertbl getOrder(Integer id) {
		Ordertbl order = (Ordertbl) findById(Ordertbl.class, id);
		return order;
	}

	public boolean updOrder(Ordertbl order) {
		// TODO Auto-generated method stub
		Ordertbl tbl = (Ordertbl) findById(Ordertbl.class, order.getId());
		// tbl.setDescription(table.getDescription());
		// tbl.setFlag(table.getFlag());
		// tbl.setNum(table.getNum());
		// tbl.setOrdId(table.getOrdId());
		try {
			update(tbl);
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return false;
	}

	public boolean delOrder(Integer id) {
		// TODO Auto-generated method stub
		try {
			delById(Ordertbl.class, id);
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return false;
	}

	public boolean addOrder(Ordertbl order) {
		// TODO Auto-generated method stub
		try {
			save(order);
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return false;
	}

	public List getTableList() {
		// TODO Auto-generated method stub
		String hql = "from Tabletbl order by id";
		return getResult(hql, null);
	}

	public List getMenuList() {
		// TODO Auto-generated method stub
		String hql = "from Menutbl order by id";
		return getResult(hql, null);
	}

	public List<Object[]> findReportByMouth(String time) {
		String hql = "select sum(ord.num*m.price),SubString(t.orderTime,1,10)";
		String sql = "from Ordertbl t,Orderdetailtbl ord,Menutbl m where t.id=ord.ordertbl and ord.menutbl=m.id and  t.orderTime like '"+dateTime.substring(0, 7)+"%' group by substr(t.orderTime,1,10)";
		Query query = sessionFactory.getCurrentSession().createQuery(hql + sql);
		return query.list();
	}
	
	public List<Object[]> findReportByYear(String time) {
		String hql = "select sum(ord.num*m.price),SubString(t.orderTime,1,4)";
		String sql = "from Ordertbl t,Orderdetailtbl ord,Menutbl m where t.id=ord.ordertbl and ord.menutbl=m.id and  t.orderTime like '2018%' group by substr(t.orderTime,1,4)";
		Query query = sessionFactory.getCurrentSession().createQuery(hql + sql);
		return query.list();
	}

	@Override
	public List<Object[]> findReportByDay(String string) {
	
		String hql = "select sum(ord.num*m.price),SubString(t.orderTime,1,10)";
		String sql = "from Ordertbl t,Orderdetailtbl ord,Menutbl m where t.id=ord.ordertbl and ord.menutbl=m.id and  t.orderTime like '"+dateTime+"%' group by substr(t.orderTime,1,10)";
		Query query = sessionFactory.getCurrentSession().createQuery(hql + sql);
		return query.list();
	}

	@Override
	public boolean addPurchase(Purchase purchase) {
		// TODO Auto-generated method stub
				try {
					save(purchase);
					return true;
				} catch (Exception e) {
					// TODO: handle exception
					e.printStackTrace();
				}
				return false;
	}

	@Override
	public List getPurchase() {
		String hql = "from Purchase order by createTime desc";
		return getResult(hql, null);
	}

	@Override
	public List<Object[]> findPurchaseByMouth() {

		String hql = "select sum(cost),SubString(createTime,1,10)";
		String sql = "from Purchase p left outer join Ordertbl o on p.orderTime=o.createTime and orderTime like '"+dateTime+"%' group by substr(p.orderTime,1,10)";
		Query query = sessionFactory.getCurrentSession().createQuery(hql + sql);
		return query.list();
	}

}
